using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Text;
using System.Data.Common;

namespace CUSTOMS.Modules.HS.Components
{
    public enum SearchMethod
    {
        AllWord = 0,
        AnyWord = 1,
        Exact = 2
    }

    public class Item
    {
        public DataSet SearchByChapter(string chapter)
        {
            string query = string.Format("SELECT * FROM t_HS_Items WHERE LEFT(ID, 2) = '{0}' ORDER BY ID", chapter);
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(query);
            return db.ExecuteDataSet(dbCommand);
        }
        
        //-----------------------------------------------------------------------------------------
        
        public DataSet Search(string hsCode, string description, string searchOperator, SearchMethod searchMethod)
        {
            StringBuilder query = new StringBuilder(string.Empty);
            query.Append("SELECT ");
            query.Append("* ");
            query.Append("FROM t_HS_Items ");
            query.Append("WHERE ");
            if (hsCode.Length > 0)
            {
                query.Append(string.Format("(LEFT(ID, 4) + ID02 + ID03 + ID04 LIKE '{0}%') ", hsCode));
            }
            else
            {
                query.Append("(1 = 1)");
            }

            query.Append(" " + searchOperator + " ");

            if (description.Length > 0)
            {

                string[] words;
                switch (searchMethod)
                {
                    case SearchMethod.AllWord:
                        words = description.Split(' ');
                        foreach (string word in words)
                        {
                            query.Append(string.Format("(Description LIKE N'%{0}%') AND ", word));
                        }
                        // Remove last AND in sql query.
                        query.Remove(query.Length - 5, 5);
                        break;
                    case SearchMethod.AnyWord:
                        words = description.Split(' ');
                        query.Append("(");
                        foreach (string word in words)
                        {
                            query.Append(string.Format("(Description LIKE N'%{0}%') OR ", word));
                        }
                        // Remove last OR in sql query.
                        query.Remove(query.Length - 4, 4);
                        query.Append(")");
                        break;
                    case SearchMethod.Exact:
                        query.Append(string.Format("(Description LIKE N'%{0}%')", description));
                        break;
                }
            }
            else
            {
                query.Append("(2 = 2)");
            }
            query.Append(" ORDER BY ID");

            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(query.ToString());
            return db.ExecuteDataSet(dbCommand);
        }
    }
}
